﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MYSQL_db.Model;
using MySql.Data.MySqlClient;

namespace MYSQL_db.DAL
{
    class  OperatorDAL
    {
        //把公共的代码封装到一个方法中，这样可以避免重复性的代码，提高代码复用性
        private static Operator ToOperator(DataRow row)
        {   //把从数据库中查询到的行中字段值，封装到对象中
            Operator opt = new Operator();
            opt.Id = (long)row["Id"];
            opt.Uid = (string)row["Uid"];
            opt.Name = (string)row["Name"];          
            opt.Password = (string)row["Password"];
            opt.Position = (string)row["Position"];
            opt.ShopNum = (string)row["ShopNum"];
            return opt;
        }

        //通过UId查询返回操作者对象
        public static Operator GetById(string Uid)
        {
            DataTable dt = SqlHelper.ExecuteDataTable("select * from T_Operator where Uid=@Uid",
                new MySqlParameter("@Uid", Uid));
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            else if (dt.Rows.Count > 1)
            {
                throw new Exception("严重错误，查出多条数据！");
            }
            else
            {
                DataRow row = dt.Rows[0]; //获取返回表中的第一行数据
                return ToOperator(row);    //将返回行中数据封装到Operator对象中
            }
        }
        //通过工号删除操作者
        public static int DeleteById(Operator opt)
        {
            return
            SqlHelper.ExecuteNonQuery("delete from T_Operator where Uid=@Uid and Name=@Name",
                new MySqlParameter("@Uid", opt.Uid), new MySqlParameter("@Name", opt.Name));
        }
        //Operator对象作为参数，函数中取出属性值插入数据库
        public static int Insert(Operator opt)
        {
            return
            SqlHelper.ExecuteNonQuery(@"INSERT INTO T_Operator
                       (Uid
                       ,Name
                       ,Password
                       ,Position
                       ,ShopNum
                        )
                 Values(@Uid, @Name,@Password,@Position,@ShopNum)", new MySqlParameter("@Uid", opt.Uid),
                                               new MySqlParameter("@Name", opt.Name),
                                               new MySqlParameter("@Password", opt.Password),
                                               new MySqlParameter("@Position", opt.Position),
                                               new MySqlParameter("@ShopNum", opt.ShopNum)
                                               );
        }
        //传入一个Operator对象，提取对象属性，修改数据库值
        public static int Update(Operator opt)
        {
            return
            SqlHelper.ExecuteNonQuery(@"UPDATE T_Operator
                       SET Uid = @Uid
                          ,Name = @Name
                          ,Password = @Password
                          ,Position = @Position
                          ,ShopNum=@ShopNum
                            WHERE Uid=@Uid",
                                  new MySqlParameter("@Uid", opt.Uid),
                                  new MySqlParameter("@Name", opt.Name),
                                  new MySqlParameter("@Password", opt.Password),
                                  new MySqlParameter("@Position", opt.Position),
                                   new MySqlParameter("@ShopNum", opt.ShopNum)
                               );
        }
        //无参数查询数据库，返回Operator对象数组
        public static Operator[] GetAll()
        {
      
            DataTable table = SqlHelper.ExecuteDataTable("select * from T_Operator");
            Operator[] opts = new Operator[table.Rows.Count];
            for (int i = 0; i < table.Rows.Count; i++)
            {
                DataRow row = table.Rows[i]; //循环取出表中每一行

                opts[i] = ToOperator(row); //把取到的每一行加入对象数组
            }
            return opts; //返回对象数组
        }



    }
}
